package com.power.sifa.correction.dao.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.plf.core.common.dao.OffersetTransformers;
import com.power.sifa.correction.model.Crc_person;
import com.power.sifa.correction.model.Cre_warning;
import com.power.sifa.correction.model.enumeration.Tp_crc_userEnum;
import com.power.sifa.correction.model.pageInfo.Crc_personPageInfo;
import com.power.sifa.statistics.model.vo.MonitoringVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import com.power.sifa.correction.dao.Crc_personDaoCustom;

import javax.annotation.Resource;
import javax.persistence.Column;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.*;

/**
 * 社区矫正人员信息 持久层实现(hibernate)
 */

@Repository
@Slf4j
public class Crc_personDaoImpl implements Crc_personDaoCustom {


    @Value("${power.file.http-schema:''}")
    private String httpSchema;
    @Value("${power.file.domain:''}")
    private String domain;
    @Value("${minio.bucket:''}")
    private String rebucket;
    @Resource
    private EntityManager entityManager;


    private StringBuffer generateSql(Crc_personPageInfo pageInfo, List<Object> paramsList) {
        // 删除标志 1:为删除
        StringBuffer sql = new StringBuffer(" select * from Crc_person s where 1 = 1 and s.deleted = 0  ");
        int index = 1;
        // 姓名
        if (StringUtils.isNotEmpty(pageInfo.getF_name())) {
            sql.append(" and s.name like ?" + index++);
            paramsList.add("%" + pageInfo.getF_name() + "%");
        }
        // 罪名
        if (StringUtils.isNotEmpty(pageInfo.getF_crime())) {
            sql.append(" and s.crime like ?" + index++);
            paramsList.add("%" + pageInfo.getF_crime() + "%");
        }
        // 身份证号
        if (StringUtils.isNotEmpty(pageInfo.getF_cardno())) {
            sql.append(" and s.cardno like ?" + index++);
            paramsList.add("%" + pageInfo.getF_cardno() + "%");
        }
        // 身份证号
        if (StringUtils.isNotEmpty(pageInfo.getF_cardnoindex())) {
            sql.append(" and s.cardnoindex like ?" + index++);
            paramsList.add("%" + pageInfo.getF_cardnoindex() + "%");
        }
        // 性别
        if (ObjectUtil.isNotEmpty(pageInfo.getF_gender())) {
            sql.append(" and s.gender = ?" + index++);
            paramsList.add(pageInfo.getF_gender());
        }
        // 矫正类型
        if (ObjectUtil.isNotEmpty(pageInfo.getF_crctype())) {
            sql.append(" and s.crctype = ?" + index++);
            paramsList.add(pageInfo.getF_crctype());
        }
        // 司法所id
        if (ObjectUtil.isNotEmpty(pageInfo.getF_deptid())) {
            sql.append(" and s.deptid = ?" + index++);
            paramsList.add(pageInfo.getF_deptid());
        }
        // 是否是重点人员
        if (ObjectUtil.isNotNull(pageInfo.getF_focus())) {
            sql.append(" and s.focus = ?" + index++);
            paramsList.add(pageInfo.getF_focus());
        }
        // 矫正开始时间
        if (ObjectUtil.isNotEmpty(pageInfo.getF_startdate())) {
            sql.append(" and s.startdate >= ?" + index++);
            paramsList.add(pageInfo.getF_startdate());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_startdate_01())) {
            sql.append(" and s.startdate <= ?" + index++);
            paramsList.add(pageInfo.getF_startdate_01());
        }
        // 矫正结束时间
        if (ObjectUtil.isNotEmpty(pageInfo.getF_enddate())) {
            sql.append(" and s.enddate >= ?" + index++);
            paramsList.add(pageInfo.getF_enddate());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_enddate_01())) {
            sql.append(" and s.enddate <= ?" + index++);
            paramsList.add(pageInfo.getF_enddate_01());
        }

        // 排序
        sql.append(" order by s." + pageInfo.getSort() + " " + pageInfo.getOrder());
        return sql;

    }


    @Override
    public List<Crc_person> pageQuery(Crc_personPageInfo pageInfo) {
        List<Crc_person> result = new ArrayList<>();
        List<Object> paramsList = new ArrayList<>();
        StringBuffer sql = generateSql(pageInfo, paramsList);
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());


        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Crc_person.class);
            }
        }
        return result;
    }


    @Override
    public List<Crc_person> listQuery(Crc_personPageInfo pageInfo) {
        List<Crc_person> result = new ArrayList<>();
        List<Object> paramsList = new ArrayList<>();
        StringBuffer sql = generateSql(pageInfo, paramsList);
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());


        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        List<Map> list = query.getResultList();
        if (CollectionUtil.isNotEmpty(list)) {
            result = JSON.parseArray(JSON.toJSONString(list), Crc_person.class);
        }
        return result;
    }

    @Override
    public Crc_person queryById(String id) {
        Query query = entityManager.createNativeQuery("select a.*, b.name as deptname from Crc_person a left join ha_depart b on a.deptid = b.id where a.id = ?");
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        query.setParameter(1, id);
        Map result = (Map) query.getSingleResult();
        return JSON.parseObject(JSON.toJSONString(result), Crc_person.class);
    }

    @Override
    public Crc_person findDeptById(String personId) {
        StringBuffer sql = new StringBuffer("select p.*, v.orgname as deptname, a.path as avatarUrl, a.bucket as bucket" +
                " from crc_person p" +
                " left join v_rel_dept_correctuser v on p.deptid = v.adid" +
                " left join ha_attachment a on p.avatar = a.id" +
                " where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(personId)) {
            sql.append(" and p.id =?" + index++);
            paramsList.add(personId);
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        Crc_person result = null;
        Map<String, Object> map = (Map<String, Object>) query.getSingleResult();
        if (CollectionUtil.isEmpty(map)) {
            return null;
        }
        String path = (String) map.get("avatarurl");
        String bucket = (String) map.get("bucket");
        String avatarUrl = httpSchema + domain + "/" + bucket + "/" + path;
        map.put("avatarUrl", avatarUrl);
        result = JSON.parseObject(JSON.toJSONString(map), Crc_person.class);
        return result;
    }

    @Override
    public Crc_person findPersonById(String id) {
        StringBuffer sql = new StringBuffer("select p.*, e.description as nation_text, a.path as path, a.bucket as bucket, d.name as deptname " +
                "from crc_person p " +
                "left join ha_enumerationvalue e on p.nation = e.id " +
                "left join ha_attachment a on p.avatar = a.id " +
                "left join ha_depart d on p.deptid = d.id " +
                "where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(id)) {
            sql.append(" and p.id =?" + index++);
            paramsList.add(id);
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        Crc_person result = null;
//        Map<String, Object> map = (Map<String, Object>) query.getSingleResult();
        List<Map<String, Object>> list = query.getResultList();
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        Map<String, Object> map = list.get(0);
        String path = (String) map.get("path");
        String bucket = (String) map.get("bucket");
        String avatarUrl = httpSchema + domain + "/" + bucket + "/" + path;
        map.put("avatarUrl", avatarUrl);
        map.put("crctype_text", Tp_crc_userEnum.getTypeNameByValue((Integer) map.get("crctype")));
        result = JSON.parseObject(JSON.toJSONString(map), Crc_person.class);
        return result;
    }

    @Override
    public Integer getNextval() {
        StringBuffer sql = new StringBuffer("select nextval('crc_person_id_seq')");
        Query query = entityManager.createNativeQuery(sql.toString());
        Object result = query.getSingleResult();
        return Integer.parseInt(result.toString());
    }

    @Override
    public List<Map<String, Object>> countTopCrimeList(List deptIds) {
//        StringBuffer sql = new StringBuffer("select crime, count(crime) as count from crc_person where 1 = 1 focus = 3200 ");
        StringBuffer sql = new StringBuffer("select crime, count(crime) as count from crc_person where 1 = 1 ");

        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(deptIds)) {
            sql.append(" and deptid in ?" + index++);
            paramsList.add(deptIds);
        }
        sql.append(" group by crime order by count desc limit 6");

        Query query = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        List<Object[]> resultList = query.getResultList();
        List<Map<String, Object>> resultListMap = new ArrayList<>();

        for (Object[] result : resultList) {
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("crime", result[0]);
            resultMap.put("count", result[1]);
            resultListMap.add(resultMap);
        }

        return resultListMap;
    }


    @Override
    public List findByDeptID(List fDeptid) {
        StringBuffer sql = new StringBuffer("select p.*, d.orgname as deptname, a.path as avatarUrl, a.bucket as bucket " +
                "from crc_person p " +
                "left join di_rel_dept_correct d on d.adid = p.deptid " +
                "left join ha_attachment a on a.id = p.avatar " +
                "where p.deleted = 0");

        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(fDeptid)) {
            sql.append(" and p.deptid in ?" + index++);
            paramsList.add(fDeptid);
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }

        List<Crc_person> result = null;
        List<Map> list = query.getResultList();
        if (CollectionUtil.isNotEmpty(list)) {
            for (Map<String, Object> map : list) {
                String path = (String) map.get("avatarurl");
                String bucket = (String) map.get("bucket");
                String avatarUrl = httpSchema + domain + "/" + bucket + "/" + path;
                map.put("avatarUrl", avatarUrl);
                map.remove("avatarurl");
            }
            result = JSON.parseArray(JSON.toJSONString(list), Crc_person.class);
        }
        return result;
    }
}
